<?php
/**
 * Created by PhpStorm.
 * User: huawei
 * Date: 2021/11/2
 * Time: 17:39
 */

namespace app\common\model;
use think\Model;
use think\Db;
class Access extends Model
{
    protected $autoWriteTimestamp = false;
    protected $rule = [
                    'type' => 'mod', // 分表方式
                    'num'  => 4     // 分表数量
                ];
    public function add($data=[]){
        $id = Db::name('access')->insertGetId(["title"=>1]);
        Db::name('access')->partition(["id"=>$id],"id",$this->rule)->insert([
            "id"=>$id,
            "userid"=>$data["userid"],
            "suser"=>isset($data["suser"])?$data["suser"]:null,
            "organizationCode"=>isset($data["organizationCode"])?$data["organizationCode"]:null,
            "poster"=>isset($data["poster"])?$data["poster"]:null,
            "visitorStatus"=>isset($data["visitorStatus"])?$data["visitorStatus"]:null,
            "phone"=>isset($data["phone"])?$data["phone"]:null,
            "newStatus"=>isset($data["newStatus"])?$data["newStatus"]:null,
            "add_time"=>time(),
        ]);
    }

    /**
     * @title 数量统计
     * @param string $where
     * @return string
     */
    public function countSql($where=''){
        $table = "yuexiu_access";
        $idKey = "id";
        $fields = "";
        $num = 4;

        if(is_string($where)){
            $where = " AND ".$where;
        }else if(is_array($where)){
            $w = " AND";
            foreach ($where as $key=>$val){
                if(is_numeric($val)){
                    $w .=" AND {$key}='{$val}'";
                }else{
                    $w .=" AND {$key}='{$val}'";
                }
            }
            $where = $w;
        }
        $countTable = [];

        for ($i = 0; $i < $num; $i++) {
            $countTable[] = sprintf('SELECT %s FROM %s_%s where 1=1 %s',$idKey, $table, ($i + 1), $where);
        }
        $countTable = '( ' . implode(" UNION ", $countTable) . ') AS ' . $table;
        $sql = "select count($idKey) as num from ".$countTable;


        return Db::query($sql);
    }

    /**
     * @title 分组查询
     * @param $where
     * @param $group
     * @return mixed
     */
    public function groupSql($where,$group=null){
        $table = "yuexiu_access";
        $idKey = "id";
        $fields = "";
        $num = 4;

        if(is_string($where)){
            $where = " AND ".$where;
        }else if(is_array($where)){
            $w = " AND";
            foreach ($where as $key=>$val){
                if(is_numeric($val)){
                    $w .=" AND {$key}='{$val}'";
                }else{
                    $w .=" AND {$key}='{$val}'";
                }
            }
            $where = $w;
        }
        $countTable = [];

        for ($i = 0; $i < $num; $i++) {
            $countTable[] = sprintf('SELECT %s FROM %s_%s where 1=1 %s',$group?($idKey.",".$group):$idKey , $table, ($i + 1), $where);
        }
        $countTable = '( ' . implode(" UNION ", $countTable) . ') AS ' . $table;
        $sql = "select * from ".$countTable;
        if($group){
            $sql .=" GROUP BY {$group}";
        }

        return Db::query($sql);
    }
    /**
     * 构造获取总记录数及主键ID的sql子查询语句
     * @param $table 主表名称
     * @param $idKey 主键id字段名称
     * @param string $fields 其它字段名称,多个字段用英文逗号分隔
     * @param int $num 子表数量
     * @param int $offset 当前页
     * @param int $rows 每页多少条
     * @param string $where 查询条件
     * @return array
     * @throws \think\Exception
     */

    public static function buildPartitionSql($table,$idKey,$fields='',$num=4,$where='',$offset=1,$rows=10) {
        if(is_string($where)){
            $where = " AND ".$where;
        }else if(is_array($where)){
            $w = " AND";
            foreach ($where as $key=>$val){
                if(is_numeric($val)){
                    $w .=" AND {$key}='{$val}'";
                }else{
                    $w .=" AND {$key}='{$val}'";
                }
            }
            $where = $w;
        }

        $countTable = [];
        $listTable = [];
        $fieldList = [$idKey];
        if ($fields) {
            $fieldList = array_merge($fieldList,explode(',',$fields));
            $fieldList = array_unique($fieldList);
        }
        $ulimit = ceil($rows/$num);

        $offset=($offset-1)*$ulimit;        //分页数据,跟普通分页一样,直接传参就好

        $fieldStr = implode(',',$fieldList);
        for ($i = 0; $i < $num; $i++) {
            $countTable[] = sprintf('SELECT %s FROM %s_%s where 1=1 %s', $idKey, $table, ($i + 1), $where);
            $listTable[] = sprintf('(SELECT %s FROM %s_%s where 1=1 %s ORDER BY id desc LIMIT %s,%s)', $fieldStr,$table, ($i + 1), $where,$offset,$ulimit);
        }
        $countTable = '( ' . implode(" UNION ", $countTable) . ') AS ' . $table;
        $listTable = '( ' . implode(" UNION ", $listTable) . ') AS ' . $table;


        $tables = ['countSql' =>"select count($idKey) as num from ".$countTable, 'listSql' =>"select * from". $listTable ];

        //这里返回的是两个sql语句,咱们简单点直接执行好,返回的就是数据的主键id
//        return  Db::connect('db_club')->query();

        return $tables;
    }
    /**
     * 构造获取指定id对应记录的sql子查询语句
     * @param $table 主表名称
     * @param $idKey 指定的id字段名称
     * @param $idValues 指定的id字段值
     * @param int $num 子表数量
     * @return string
     */
    public static function buildPartitionListSql($table,$idKey,$idValues,$num=1) {
        $idValues=array_column($idValues,'id');
        $sql = '';
        $ids = is_array($idValues) ? implode(',',$idValues) : $idValues;

        if ($ids) {
            $listTable = [];
            for ($i = 0; $i < $num; $i++) {
                $listTable[] = sprintf('SELECT * FROM %s_%s where %s in (%s)', $table, ($i + 1), $idKey, $ids);
            }
            $sql = '( ' . implode(" UNION ", $listTable) . ') AS ' . $table;
        }
        return "select * from". $sql." order by id desc";
//        return   Db::connect('db_club')->query();

    }
}